package com.geline.sqlbackup.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.geline.sqlbackup.bean.*;
import com.geline.sqlbackup.druid.DdlSqlHandler;
import com.geline.sqlbackup.druid.KingbaseDdlSqlHandler;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author: mengx
 * @date: 2021/10/16 18:13
 */
@Slf4j
public class KingbaseInformationUtil {

    /**
     * 解析表获取TableColumnInfo
     * @param dataSource
     * @param table
     * @param useRemoveTableHead
     * @param useCamelCase
     * @return
     */
    public static TableColumnInfo parseTableColumnInfo(DataSource dataSource, String table, boolean useRemoveTableHead, boolean useCamelCase){
        TableColumnInfo tableColumnInfo = new TableColumnInfo();
        InformationTable informationTable = getInformationTable(dataSource, table);
        tableColumnInfo.setTableName(table);
        tableColumnInfo.setTableComment(informationTable.getTableComment());
        tableColumnInfo.setDbType(DbType.kingbase.name());
        String entityName = JavaPackageKit.getTableEntityName(useRemoveTableHead, useCamelCase, table);
        tableColumnInfo.setEntityName(entityName);
        List<ColumnInfo> columnList = getColumnInfoList(dataSource, table);
        tableColumnInfo.setColumnInfoList(columnList);
        return tableColumnInfo;
    }

    /**
     * 查询一条记录
     * @param dataSource
     * @param tableName
     * @return
     */
    public static InformationTable getInformationTable(DataSource dataSource, String tableName){
        String catalog = JdbcParseUtils.findCatalog(dataSource);
        String database = JdbcParseUtils.findSchemaName(dataSource);
        StringBuilder sb = new StringBuilder();
        sb.append("select table_name, table_name as table_comment from information_schema.TABLES");
        sb.append(" where table_type='BASE TABLE'");
        if(StrUtil.isNotBlank(catalog)){
            sb.append(" and table_catalog='"+catalog+"'");
        }
        if(StrUtil.isNotBlank(database)){
            sb.append(" and table_schema='"+database+"'");
        }
        if(StrUtil.isNotBlank(tableName)){
            sb.append(" and table_name='"+tableName+"'");
        }
        String sql = sb.toString();
        log.debug("getInformationTable sql >>>>> {}", sql);
        List<InformationTable> informationTableList = SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
        if(informationTableList != null && informationTableList.size()>0){
            return informationTableList.get(0);
        }
        throw new RuntimeException("no exist table : "+tableName);
    }

    /**
     * 查询数据库中所有表名列表(指定数据库)
     * @param dataSource
     * @return
     */
    public static List<InformationTable> getInformationTableList(DataSource dataSource){
        //无table_comment，table_schema指数据库名, table_catalog指用户
        String database = JdbcParseUtils.findSchemaName(dataSource);
        String catalog = JdbcParseUtils.findCatalog(dataSource);
        StringBuilder sb = new StringBuilder();
        sb.append("select table_name, table_name as table_comment from information_schema.TABLES");
        sb.append(" where 1=1 and table_type='BASE TABLE'");
        if(StrUtil.isNotBlank(database)){
            sb.append(" and table_schema='"+database+"'");
        }
        if(StrUtil.isNotBlank(catalog)){
            sb.append(" and table_catalog='"+catalog+"'");
        }
        String sql = sb.toString();
        log.debug("getInformationTableList sql >>>>> {}", sql);
        return SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
    }

    /**
     * 查询kingbase字段信息
     * @param dataSource
     * @param table
     * @return
     */
    public static List<ColumnInfo> getColumnInfoList(DataSource dataSource, String table) {
        // jdbc:kingbase8://192.168.10.111:54321/sd?currentSchema=gdnyt
        String database = JdbcParseUtils.findSchemaName(dataSource);
        String sql = "SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, UDT_NAME, character_maximum_length, numeric_precision, numeric_scale " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE table_name = '"+table+"' AND table_schema = '"+database+"' " +
                "order by ORDINAL_POSITION asc";
        log.debug("getColumnInfoList sql >>>>> {}", sql);
        DdlSqlHandler ddlSqlHandler = new KingbaseDdlSqlHandler();
        List<Map<String, Object>> mapList = SqlRunnerUtils.selectMaps(dataSource, sql);
        List<KingbaseColumn> dataList = mapList.stream().map(row -> BeanUtil.toBean(row, KingbaseColumn.class)).collect(Collectors.toList());
        List<ColumnInfo> baseColumns = new ArrayList<>();
        dataList.stream().forEach(row ->{
            String columnName = row.getColumnName();
            String columnDefault = row.getColumnDefault();
            String dataType = row.getDataType();
            String udtName = row.getUdtName();
            if(StrUtil.isNotBlank(udtName)){
                dataType = udtName;
            }
            ColumnInfo column = new ColumnInfo();
            column.setColumnName(columnName);
            column.setColumnComment("");
            column.setDefaultValue(columnDefault);
            column.setNotNull("NO".equals(row.getIsNullable()));
            Integer ordinalPosition = row.getOrdinalPosition();
            column.setPrimaryKey(ordinalPosition==1);
            column.setAutoIncrement(columnDefault != null && columnDefault.startsWith("NEXTVAL"));
            column.setIntFlag(SqlTypeUtils.isInt(dataType));
            column.setNumbericFlag(SqlTypeUtils.isNumberic(dataType));
            column.setStringFlag(SqlTypeUtils.isString(dataType));

            //设置java数据类型
            column.setJavaFieldType(ddlSqlHandler.getJavaType(columnName, dataType));
            column.setJdbcTypeCode(ddlSqlHandler.getVendorTypeNumber(columnName, dataType));
            column.setJavaFieldName(StrUtil.toCamelCase(StrUtil.lowerFirst(columnName)));//首字母小写，转驼峰命名

            Integer characterMaximumLength = row.getCharacterMaximumLength();
            Integer numericPrecision = row.getNumericPrecision();
            Integer numericScale = row.getNumericScale();
            if(characterMaximumLength != null){
                column.setLength(characterMaximumLength);
                if(dataType.toUpperCase().contains("CHAR")){
                    column.setColumnType(dataType+"("+characterMaximumLength+" char)");
                }else {
                    column.setColumnType(dataType+"("+characterMaximumLength+")");
                }
            }else if(SqlTypeUtils.isInt(dataType)){
                column.setLength(numericPrecision);
                column.setScale(numericScale);
                column.setColumnType(dataType);
            }else if(SqlTypeUtils.isNumberic(dataType)){
                int length = numericPrecision+numericScale;
                column.setLength(length);
                column.setScale(numericScale);
                column.setColumnType(dataType+"("+numericPrecision+","+numericScale+")");
            }else{
                column.setColumnType(dataType);
            }
            baseColumns.add(column);
        });
        return baseColumns;
    }

    /**
     * 创建表DDL
     * @param tableColumnInfo
     * @param schemaName
     * @return
     */
    public static String buildTableDDL(TableColumnInfo tableColumnInfo, String schemaName) {
        String table = tableColumnInfo.getTableName();
        List<ColumnInfo> columnList = tableColumnInfo.getColumnInfoList();

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE "+schemaName+"."+table+" (\n");
        List<String> primaryKeyList = new ArrayList<>();
        for(ColumnInfo column : columnList){
            String columnName = column.getColumnName();
            String columnType = column.getColumnType();
            String nullStr = column.isNotNull() ? "NOT NULL" : "NULL";
            String defaultValue = (String)column.getDefaultValue();
            boolean isPrimary = column.isPrimaryKey();

            // 替换所有BPCHAR为CHAR
            if (columnType.contains("BPCHAR")) {
                columnType = columnType.replaceAll("BPCHAR","CHAR");
            }

            if (defaultValue != null && defaultValue.contains("BPCHAR")) {
                defaultValue = defaultValue.replaceAll("BPCHAR","CHAR");

            }

            if (defaultValue != null && defaultValue.contains("NEXTVAL")) {
                defaultValue = defaultValue.replace("NEXTVAL('","NEXTVAL('"+schemaName+".");
            }

            if(isPrimary){
                primaryKeyList.add(columnName);
                if(column.isAutoIncrement()){
                    /*
                    CREATE SEQUENCE tablename_colname_seq;
                    CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq'));
                    ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
                     */
                    String defaultStr = "";
                    if(defaultValue != null){
                        defaultStr = " DEFAULT "+defaultValue;
                    }
                    sb.append(String.format("  %s %s NOT NULL%s,\n", columnName, columnType, defaultStr));
                }else {
                    //id varchar(32) NOT NULL COMMENT '主键',
                    sb.append(String.format("  %s %s NOT NULL,\n", columnName, columnType));
                }
            }else {
                String defaultStr = "";
                if(!column.isNotNull() && defaultValue==null){
                    if(columnType.equals("TIMESTAMP") || columnType.equals("TEXT") || columnType.equals("NUMERIC")){
                        defaultStr = "";
                    }else {
                        defaultStr = " DEFAULT NULL";
                    }
                }else if(defaultValue != null){
                    defaultStr = " DEFAULT "+defaultValue;
                }
                //role_name varchar(30) NOT NULL DEFAULT '0',
                sb.append(String.format("  %s %s %s%s,\n", columnName, columnType, nullStr, defaultStr));
            }
        }
        if(primaryKeyList.size()>1){
            // CONSTRAINT "{tableName}_PKEY" PRIMARY KEY (id)
            String ids = StrUtil.join(",", primaryKeyList);
            sb.append("  CONSTRAINT \""+table+"_PKEY\" PRIMARY KEY ("+ids+")\n");
        }else if(primaryKeyList.size()==1) {
            sb.append("  CONSTRAINT \""+table+"_PKEY\" PRIMARY KEY ("+primaryKeyList.get(0)+")\n");
        }
        sb.append(")");
        return sb.toString();
    }
}
